﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Security;

namespace zjbks
{
    public class ExcelToMSSql
    {
        public static int count = 0;
        public static string error = "";
        public static string connstr= ConfigurationManager.ConnectionStrings["zjbksConnectionString"].ConnectionString;
        public ExcelToMSSql()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }
        public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 5.0;";
            string strExcel = string.Format("select * from [{0}$]", strSheetName);
            DataSet ds = new DataSet();
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                OleDbDataAdapter adapter1 = new OleDbDataAdapter(strExcel, strConn);
                adapter1.Fill(ds, strSheetName);
                conn.Close();
            }

            return ds.Tables[strSheetName];
        }
        //传入excel文件的路径。
        /// <summary>
        /// 导入成绩
        /// </summary>
        /// <param name="execlpath"></param>
        public static void DataTranfScoreInfo(string execlpath)
        {
            DataTable dtExcel = ExcelToDataTable(@execlpath, "Sheet1");
            for (int i = 0; i < dtExcel.Rows.Count; i++)
            {
                InsertToDBScoreInfo(dtExcel.Rows.Count,dtExcel.Rows[i][0].ToString().Trim(), float.Parse(dtExcel.Rows[i][1].ToString()),float.Parse(dtExcel.Rows[i][2].ToString().Trim()),float.Parse(dtExcel.Rows[i][3].ToString().Trim()));

                //此处根据excel中字段，自己设定要取用的字段。
            }
        }
        /// <summary>
        /// 设置考场
        /// </summary>
        /// <param name="execlpath"></param>
        public static void DataTranfKC(string execlpath)
        {
            DataTable dtExcel = ExcelToDataTable(@execlpath, "Sheet1");
            for (int i = 0; i < dtExcel.Rows.Count; i++)
            {
                InsertToDBKC(dtExcel.Rows.Count, dtExcel.Rows[i][0].ToString().Trim(), dtExcel.Rows[i][2].ToString());

                //此处根据excel中字段，自己设定要取用的字段。
            }
        }

        /// <summary>
        /// 设置分数线
        /// </summary>
        /// <param name="execlpath"></param>
        public static void DataTranfsub(string execlpath)
        {
            DataTable dtExcel = ExcelToDataTable(@execlpath, "Sheet1");
            for (int i = 0; i < dtExcel.Rows.Count; i++)
            {
                InsertToDBsub(dtExcel.Rows.Count, dtExcel.Rows[i][0].ToString().Trim(), float.Parse(dtExcel.Rows[i][1].ToString().Trim()));

                //此处根据excel中字段，自己设定要取用的字段。
            }
        }
        /// <summary>
        /// 导入录取结果
        /// </summary>
        
        public static void DataTranfluqu(string execlpath)
        {
            DataTable dtExcel = ExcelToDataTable(@execlpath, "Sheet1");
            for (int i = 0; i < dtExcel.Rows.Count; i++)
            {
                InsertToDBluqu(dtExcel.Rows.Count, dtExcel.Rows[i][0].ToString().Trim(), dtExcel.Rows[i][1].ToString().Trim());

                //此处根据excel中字段，自己设定要取用的字段。
            }
        }

        /// <summary>
        /// 导入用户
        /// </summary>

        public static void DataTranfUser(string execlpath)
        {
            DataTable dtExcel = ExcelToDataTable(@execlpath, "Sheet1");
            for (int i = 0; i < dtExcel.Rows.Count; i++)
            {
                InsertToDBUser(dtExcel.Rows.Count, dtExcel.Rows[i][0].ToString().Trim(), dtExcel.Rows[i][1].ToString().Trim(), "考生");

                //此处根据excel中字段，自己设定要取用的字段。
            }
        }

        //插入记录到SqlServer数据库

        public static void InsertToDBScoreInfo(int countmax,string id, float math,float eng,float subject)
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            string str_insertscore = "insert into ScoreInfo (ID,Math,Eng,SubjectScore) values('" + id + "','" + math +"','"+ eng + "','" + subject + "')";
            SqlCommand cmd = new SqlCommand(str_insertscore, conn);
            try
            {
                cmd.ExecuteNonQuery();
                count++;
            }
            catch (Exception e)
            {
                error += e.Message.ToString() + "<br />";
            }
            finally
            {
                conn.Close();
            }
        }
        public static void InsertToDBsub(int countmax, string id,float SubjectScore)
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            string str_insertscore = "update [SubjectInfo] set Score='"+SubjectScore+"' where SubjectNo='"+id+"'";
            SqlCommand cmd = new SqlCommand(str_insertscore, conn);
            try
            {
                cmd.ExecuteNonQuery();
                count++;
                    
            }
            catch (Exception e)
            {
                error += e.Message.ToString() + "<br />";
            }
            finally
            {
                conn.Close();
            }
        }
        public static void InsertToDBluqu(int countmax, string id, string collage)
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            string str_insertscore = "insert into [luqu] (id,collage) values ('"+id+"','"+collage+"')";
            SqlCommand cmd = new SqlCommand(str_insertscore, conn);
            try
            {
                cmd.ExecuteNonQuery();
                count++;

            }
            catch (Exception e)
            {
                error += e.Message.ToString() + "<br />";
            }
            finally
            {
                conn.Close();
            }
        }
        public static void InsertToDBUser(int countmax, string id, string pwd,string type)
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            string str_insertscore = "insert into UserInfo (ID,Pwd,[Type]) values ('"+id+"','"+pwd+"','"+type+"')";
            SqlCommand cmd = new SqlCommand(str_insertscore, conn);
            try
            {
                cmd.ExecuteNonQuery();
                count++;

            }
            catch (Exception e)
            {
                error += e.Message.ToString() + "<br />";
            }
            finally
            {
                conn.Close();
            }
        }
        public static void InsertToDBKC(int countmax, string kc, string no)
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            string str_insertscore = "update SubjectInfo set KC='"+kc+"' where SubjectNo='"+no+"')";
            SqlCommand cmd = new SqlCommand(str_insertscore, conn);
            try
            {
                cmd.ExecuteNonQuery();
                count++;

            }
            catch (Exception e)
            {
                error += e.Message.ToString() + "<br />";
            }
            finally
            {
                conn.Close();
            }
        }
    }
}